package com.woniuxy.creditsystem.common.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Date;

/**
 * 打印java文件名和行号
 * @author hejie
 */
public class TraceUtil {
//	打印java文件名和行号
	public static String positionTrace() {
		String posTrace="[\njava文件："+Thread.currentThread().getStackTrace()[2].getFileName()+
				",行号"+Thread.currentThread().getStackTrace()[2].getLineNumber()+"]";
		return posTrace;
	}


	/**
	 * 测试Excel文档的读、写---POI
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		//测试从Excel读取数据
		readDataFromExcel();
		//测试写数据到Excel文档
//		writeDate2Excel();
	}

	/**
	 * 从Excel文档读取数据
	 * @throws Exception
	 */
	public static void readDataFromExcel() throws Exception {
		FileInputStream is = new FileInputStream("G://test1.xls");
		BufferedInputStream bis = new BufferedInputStream(is);
		POIFSFileSystem fileSystem = new POIFSFileSystem(bis);
		HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
		//获取日期
		HSSFCell cell = workbook.getSheetAt(0).getRow(0).getCell(0);
		//获取数字
		HSSFCell cell02 = workbook.getSheetAt(0).getRow(0).getCell(1);
		HSSFCell cell03 = workbook.getSheetAt(0).getRow(0).getCell(2);
		HSSFCell cell04 = workbook.getSheetAt(0).getRow(0).getCell(3);
		HSSFCell cell2_01 = workbook.getSheetAt(0).getRow(1).getCell(0);
		HSSFCell cell2_02 = workbook.getSheetAt(0).getRow(1).getCell(1);
		System.out.println("cell="+ cell.getDateCellValue()+"\ncell02="+cell02.getNumericCellValue());
		System.out.println("cell03="+ cell03.getStringCellValue()+"\ncell04="+cell04);
		System.out.println("cell2_01="+ cell2_01.getStringCellValue()+"\ncell2_02="+cell2_02);
	}

	/**
	 * 写数据到Excel文档
	 * @throws Exception
	 */
	public static void writeDate2Excel() throws Exception {
		//创建表对象，即工作簿对象
		HSSFWorkbook workbook = new HSSFWorkbook();
		//第一个sheet对象demo1
		HSSFSheet sheet = workbook.createSheet("demo1");
		//行对象
		HSSFRow row = sheet.createRow(0);
		//单元格对象
		HSSFCell cell = row.createCell(0);
		cell.setCellValue(new Date());

		//解决日期显示不正常的问题：给第1行、第2列单元格写入日期
		//用Excel表对象获取CreationHelper对象
		CreationHelper createHelper = workbook.getCreationHelper();
		//用Excel表对象创建CellStyle对象
		CellStyle cellStyle = workbook.createCellStyle();
		//给cellStyle对象设置Date格式
		cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

		HSSFCell cell2 = row.createCell(1);
		cell2.setCellStyle(cellStyle);
		cell2.setCellValue(new Date());





		/*//第二个sheet对象：demo
		HSSFWorkbook workbook1 = new HSSFWorkbook();
		//sheet对象
		HSSFSheet sheet1 = workbook1.createSheet("demo");
		for(int i = 0;i<10;i++){
			//行对象
			HSSFRow row2 = sheet1.createRow(i);
			for (int j = 0;j<10;j++){
				//单元格对象
				HSSFCell cell1 = row2.createCell(j);
				cell1.setCellValue(i+"----"+j);
			}
		}*/

		FileOutputStream os = new FileOutputStream("G://test5.xls");
//		workbook1.write(os);
		workbook.write(os);
		os.flush();
		os.close();
	}
}
